﻿CREATE PROCEDURE [admin].[s_SnapshotDatabase]
	@Date sysname = NULL --YYMMDD
AS

DECLARE @SQL nvarchar(max)

IF @Date > '' BEGIN
	SET @SQL = 'RESTORE DATABASE '+DB_Name()+' from DATABASE_SNAPSHOT = '''+DB_NAME()+'_ss_'+@Date+''''
	PRINT @SQL
	EXEC (@SQL)
	RETURN
END

DECLARE @DateStamp sysname SET @DateStamp = CONVERT(varchar,GETDATE(),12)

SELECT @SQL = ISNULL(@SQL+',','') + '
( NAME = '+name+', FILENAME = 
'''+physical_name+'_'+@DateStamp+'.ss'' )'
FROM sys.database_files WHERE type = 0

SET @SQL = 'CREATE DATABASE '+DB_NAME()+'_ss_'+@DateStamp+' ON'+@SQL + '
AS SNAPSHOT OF '+DB_NAME()+';'


PRINT @SQL
EXEC (@SQL)






